package org.lanqiao.util;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Properties;

/**
 * 封装一个连接数据库的工具类，使用配置文件jdbc.properties
 * @author WZW
 *
 */
public class SqlUtil {
	//本地线程变量,   用于连接线程
	private static ThreadLocal<Connection> local=new ThreadLocal<>();
	private static Properties properties =new Properties();
	//加载配置文件,load  类加载器
	static {
		try {
			properties.load(SqlUtil.class.getClassLoader().getResourceAsStream("jdbc.properties"));
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	//getProperty 获得属性，   加载类s的值
	static {
		try {
			Class.forName(properties.getProperty("Driver"));
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	//静态方法，建立连接
	public static Connection openConnection() {
		Connection conn=local.get();
		try {
			if(conn==null||conn.isClosed()) {
				conn=DriverManager.getConnection(properties.getProperty("URL"),properties.getProperty("USER"),properties.getProperty("PASSWORD"));
				local.set(conn);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("连接失败",e);
		}
		return conn;
	}
	//更新方法,返回受影响的行
	public static int  update (String sql,Object ...params) {
		Connection conn=SqlUtil.openConnection();
		int row=0;
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			if(params!=null) {
				for(int i=0;i<params.length;i++) {
					pst.setObject(i+1, params[i]);
				}
			}
			row=pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException("数据修改更新失败",e);
		}finally {
			if(pst!=null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			close();
		}
		return row;
	}
	//select方法，已过时，返回result
	@Deprecated
	public static ResultSet select (String sql,Object ...params) {
		Connection conn=SqlUtil.openConnection();
		ResultSet rs=null;
		PreparedStatement pst=null;
		try {
			pst=conn.prepareStatement(sql);
			if(params!=null) {
				for(int i=0;i<params.length;i++) {
					pst.setObject(i+1, params[i]);
				}
			}
			rs=pst.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
		return rs;
	}
	


/**
	 * 查询返回HashMp
	 * @param sql
	 * @param pareams
	 * @return
	 */
	public static <T>List<T> select21(String sql,Object ... pareams){
		Connection conn=SqlUtil.openConnection();
		ResultSet rs=null;
		PreparedStatement pst=null;
		List<HashMap<String,Object>> rows=new ArrayList<HashMap<String,Object>>();
		try {
			pst=conn.prepareStatement(sql);
			if(pareams!=null){
				for(int i=0;i<pareams.length;i++){
					pst.setObject(i+1, pareams[i]);
				}	
				}
			rs=pst.executeQuery();
			ResultSetMetaData rsmata =rs.getMetaData();//数据库解析完毕，==调用了子类 Mapping方法
			int length=rsmata.getColumnCount();
			while(rs.next()){
				HashMap<String,Object> map=new HashMap<String,Object>();
				for(int i=0;i<length;i++){
					String columnLabel=rsmata.getColumnLabel(i+1);
					map.put(columnLabel,rs.getObject(columnLabel));
				}
				rows.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(rs!=null){
				try {
					rs.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			if(pst!=null){
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
			close();
		}
		return (List<T>) rows;
	}

	//调用接口实现，select2，也是返回list
	public static <T> List<T> select2 (String sql,RowHandlerMapper<T> handlerMapper,Object...params){
		Connection conn=SqlUtil.openConnection();
		ResultSet rs=null;
		PreparedStatement pst=null;
		List<T> rows =null;
		try {
			pst=conn.prepareStatement(sql);
			if(params!=null) {
				for(int i=0;i<params.length;i++) {
					pst.setObject(i+1, params[i]);
				}
			}
			rs=pst.executeQuery();
			
			rows=handlerMapper.maping(rs);   //���ݽ�����Ϻ�   ====�൱�ڵ���   ������ʵ������ Maping ���� 
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			if(rs!=null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if(pst!=null) {
				try {
					pst.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
		return rows;
	}
	//常使用的select;利用上面过时的方法（返回result的那个）；；得到   List<LinkedHashMap<String,Object>>
	public static List<LinkedHashMap<String,Object>> select3(String sql,Object...params){
		
		Connection conn =SqlUtil.openConnection();
		ResultSet rs=SqlUtil.select(sql, params);
		ResultSetMetaData rsmd = null; 
		int columnCount = 0;
		try {
			rsmd = rs.getMetaData();
			// 获得结果集列数 
	        columnCount = rsmd.getColumnCount();  

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}  
	       // 创建list  
	       List<LinkedHashMap<String,Object>> list = new ArrayList<LinkedHashMap<String,Object>>();  
	  
	       try {  
	           // 将result中结果保存到list中 
	           while (rs.next()) {  
	        	   LinkedHashMap<String, Object> map = new LinkedHashMap<String, Object>();  
	               for (int i = 1; i <= columnCount; i++) {  
	            	   String camelName=SqlUtil.camelName(rsmd.getColumnLabel(i));
	                   map.put(camelName, rs.getObject(i));  
//	                   System.out.println(rsmd.getColumnLabel(i)+":"+ rs.getObject(i));
	               }  
	               list.add(map);  
	           }  
	       } catch (SQLException e) {  
	           System.out.println(e.getMessage()); 
	           e.printStackTrace();
	       }finally {
				if(rs!=null) {
					try {
						rs.close();
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
				close();
			}
	       return list; 
	}
	//关闭连接
	public static void close() {
		Connection conn=local.get();
		if(conn!=null) {
			try {
				conn.close();
				local.remove();
				conn=null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	public static interface RowHandlerMapper<T> {
		public  <T> List<T> maping(ResultSet rs);
	 }
	
	/**
	 * 将大写kebab-case改写成 PascalCase
	 * @param name
	 * @return
	 */
	public static String camelName(String name) {
	    StringBuilder result = new StringBuilder();
	    // 快速检查
	    if (name == null || name.isEmpty()) {
	        // 没必要转换
	        return "";
	    } else if (!name.contains("_")) {
	        // 不含下划线，仅将首字母小写
	        return name.substring(0, 1).toLowerCase() + name.substring(1);
	    }
	    // 用下划线将原始字符串分割
	    String camels[] = name.split("_");
	    for (String camel :  camels) {
	        // 跳过原始字符串中开头、结尾的下换线或双重下划线
	        if (camel.isEmpty()) {
	            continue;
	        }
	        // 处理真正的驼峰片段
	        if (result.length() == 0) {
	            // 第一个驼峰片段，全部字母都小写
	            result.append(camel.toLowerCase());
	        } else {
	            // 其他的驼峰片段，首字母大写
	            result.append(camel.substring(0, 1).toUpperCase());
	            result.append(camel.substring(1).toLowerCase());
	        }
	    }
	    return result.toString();
	}
}
